package com.xindacloud.sms.console.system.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.xindacloud.sms.console.constant.SessionKeys;
import com.xindacloud.sms.console.result.Result;
import com.xindacloud.sms.console.result.ResultGenerator;
import com.xindacloud.sms.console.system.dto.*;
import com.xindacloud.sms.console.system.entity.SmsPricing;
import com.xindacloud.sms.console.system.mapper.*;
import com.xindacloud.sms.console.system.service.ISmsPricingService;
import com.xindacloud.sms.console.system.vo.SearchSmsPricingPageVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * <p>
 * 价格列表 服务实现类
 * </p>
 *
 * @author czc
 * @since 2019-10-08
 */
@Service
@Slf4j
public class SmsPricingServiceImpl extends ServiceImpl<SmsPricingMapper, SmsPricing> implements ISmsPricingService {

    private final SmsPricingMapper smsPricingMapper;

    private final SmsAgentMapper smsAgentMapper;

    @Autowired
    private SmsAgentGroupMapper smsAgentGroupMapper;

    @SuppressWarnings("SpringJavaInjectionPointsAutowiringInspection")
    @Autowired
    public SmsPricingServiceImpl(SmsPricingMapper smsPricingMapper, SmsAgentMapper smsAgentMapper) {
        this.smsPricingMapper = smsPricingMapper;
        this.smsAgentMapper = smsAgentMapper;
    }

    @Override
    public Result getPricingList(SmsPricingDTO smsPricingDTO) {
        IPage<SmsPricing> page = new Page<>(smsPricingDTO.getPageNum(), smsPricingDTO.getPageSize());
        QueryWrapper<SmsPricing> wrapper = new QueryWrapper<>();
        if (smsPricingDTO.getAgentId() != null) {
            wrapper.eq("agent_id", smsPricingDTO.getAgentId());
        }
        if (smsPricingDTO.getCountryName() != null) {
            wrapper.eq("country_name", smsPricingDTO.getCountryName());
        }
        if (smsPricingDTO.getCountryName() != null) {
            wrapper.orderByDesc("country_name");
        }
        if (smsPricingDTO.getCostPrice() != null) {
            wrapper.orderByDesc("cost_price");
        }
        if (smsPricingDTO.getNetworkName() != null) {
            wrapper.orderByDesc("network_name");
        }
        List spList = smsPricingMapper.getPriceListByOperator(smsPricingDTO);
        page.setRecords(spList);
        return ResultGenerator.genSuccessResult(this.page(page, wrapper));
        //return ResultGenerator.genSuccessResult(spList);
    }

    @Autowired
    private SmsUserRoleMapper smsUserRoleMapper;
    @Autowired
    private SmsRoleMapper smsRoleMapper;

    @Override
    public IPage<SearchSmsPricingPageVO> queryPricingPageBy(SearchSmsPricingDTO searchSmsPricingDTO) {
        Page<SearchSmsPricingPageVO> pricingPage = new Page<>(searchSmsPricingDTO.getPageNum(), searchSmsPricingDTO.getPageSize());
        IPage<SearchSmsPricingPageVO> pageResult = null;

        //根据用户id获取角色
       /* QueryWrapper<SmsUserRole> userRoleWrapper = new QueryWrapper<>();
        userRoleWrapper.eq("user_id", searchSmsPricingDTO.getOperatorId());
        List<SmsUserRole> surList = smsUserRoleMapper.selectList(userRoleWrapper);
        Set<Integer> iSet = new HashSet<>();
        for (SmsUserRole smsUserRole : surList) {
            QueryWrapper<SmsRole> roleWrapper = new QueryWrapper<>();
            roleWrapper.eq("role_id", smsUserRole.getRoleId());
            List<SmsRole> smsRoleList = smsRoleMapper.selectList(roleWrapper);
            for (SmsRole smsRole : smsRoleList) {
                iSet.add(smsRole.getRolePrivLevel());
            }
        }
        Integer roleLevel = 0;
        if (iSet != null && iSet.size() > 0) {
            roleLevel = Collections.max(iSet);
        }*/
        //

        /*if (roleLevel != null && roleLevel.equals(1)) {
            pageResult = smsPricingMapper.selectSalesPagePricingPageVo(pricingPage, searchSmsPricingDTO);
        } else {*/
        pageResult = smsPricingMapper.selectPagePricingPageVo(pricingPage, searchSmsPricingDTO);
        //}

        return pageResult;
    }

    @Override
    public Result updateStatus(SmsPricingDTO smsPricingDTO) {
        SmsPricing smsPricing = this.getById(smsPricingDTO.getOperatorId());
        smsPricing.setStatus(smsPricingDTO.getStatus());
        if (this.updateById(smsPricing)) {
            return ResultGenerator.genSuccessResult("状态修改成功!");
        } else {
            return ResultGenerator.genClientFailResult("状态修改失败!");
        }
    }

   /* @Override
    public Result importPriceExcel(MultipartFile file, String agentId) {

        QueryWrapper<SmsAgent> queryWrapper = new QueryWrapper();
        queryWrapper.eq("agent_name", "sinch");
        SmsAgent agentEntity = smsAgentMapper.selectOne(queryWrapper);
        List<SmsPricing> infoList = new ArrayList<>();

        try {
            final String originalFilename = file.getOriginalFilename();
            assert originalFilename != null;
            if (originalFilename.substring(originalFilename.lastIndexOf(".")).equalsIgnoreCase(".csv")) {
                infoList.addAll(readCSVFile(file.getInputStream(), agentId));
            } else {
                infoList.addAll(readExcel(file.getInputStream(), agentId));

            }
        } catch (IOException | InvalidFormatException e) {
            //e.printStackTrace();
            log.error(e.getMessage(), e);
        }
        if (this.saveBatch(infoList)) {
            return ResultGenerator.genSuccessResult("导入Excel成功!");
        } else {
            return ResultGenerator.genClientFailResult("导入Excel失败!");
        }

    }*/

    @Override
    public Result importPriceExcel(MultipartFile file, String agentId) {
        List<SmsPricing> infoList = new ArrayList<>();
       /* SmsAgent smsAgent = smsAgentMapper.selectById(agentId);
        SmsAgentGroup smsAgentGroup=smsAgentGroupMapper.selectById(smsAgent.getGroupId());
        String agentGroup=smsAgentGroup.getGroupName();*/
        try {
            final String originalFilename = file.getOriginalFilename();
            assert originalFilename != null;
            if (originalFilename.substring(originalFilename.lastIndexOf(".")).equalsIgnoreCase(".csv")) {
                /*if (agentGroup.equalsIgnoreCase("nexmo")) {
                    infoList.addAll(readNexmoCSVFile(file.getInputStream(), agentId));
                } else if (agentGroup.equalsIgnoreCase("sinch")) {
                    infoList.addAll(readSinchCSVFile(file.getInputStream(), agentId));
                } else if (agentGroup.equalsIgnoreCase("cm")) {
                    infoList.addAll(readCmCSVFile(file.getInputStream(), agentId));
                } else {

                }*/
                infoList.addAll(readTemplageExcel(file.getInputStream(), agentId));

            } else {
                /*if (agentGroup.equalsIgnoreCase("nexmo")) {
                    infoList.addAll(readNexmoExcel(file.getInputStream(), agentId));
                } else if (agentGroup.equalsIgnoreCase("sinch")) {
                    infoList.addAll(readSinchExcel(file.getInputStream(), agentId));
                } else if (agentGroup.equalsIgnoreCase("cm")) {
                    infoList.addAll(readCmExcel(file.getInputStream(), agentId));
                } else {

                }*/
                infoList.addAll(readTemplageExcel(file.getInputStream(), agentId));

            }
        } catch (IOException | InvalidFormatException e) {
            log.error(e.getMessage(), e);
        }
        if (this.saveBatch(infoList)) {
            return ResultGenerator.genSuccessResult("导入Excel成功!");
        } else {
            return ResultGenerator.genClientFailResult("导入Excel失败!");
        }

    }

    private List<SmsPricing> readExcel(InputStream inputStream, String agentId) throws IOException, InvalidFormatException {
        List<SmsPricing> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (Objects.isNull(row)) {
                continue;
            }
            SmsPricing info = new SmsPricing();
            if (Objects.nonNull(row.getCell(0))) {
                info.setCountryCode(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                info.setCountryName(row.getCell(1).getStringCellValue());
                info.setCountryDisplayName(row.getCell(1).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(2))) {
                info.setNetworkCode(row.getCell(2).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(3))) {
                info.setNetworkAliases(row.getCell(3).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(4))) {
                info.setNetworkName(row.getCell(4).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(5))) {
                info.setPrefix(row.getCell(5).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(6))) {
                info.setPrefix(row.getCell(5).getStringCellValue());
                final BigDecimal price = BigDecimal.valueOf(row.getCell(6).getNumericCellValue());
                info.setPrice(price);
                info.setCostPrice(price);
                info.setDefaultPrice(price);
            }
            if (Objects.nonNull(row.getCell(0)) && Objects.nonNull(row.getCell(5))) {
                info.setCurrency("EUR");
                info.setAgentId(agentId);
                info.setType("mobile");
                smsPricingList.add(info);
            }
        }
        return smsPricingList;
    }

    private List<SmsPricing> readTemplageExcel(InputStream inputStream, String agentId) throws IOException, InvalidFormatException {
        List<SmsPricing> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (Objects.isNull(row)) {
                continue;
            }
            SmsPricing info = new SmsPricing();
            if (Objects.nonNull(row.getCell(0))) {
                row.getCell(0).setCellType(CellType.STRING);
                info.setCountryCode(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                row.getCell(1).setCellType(CellType.STRING);
                info.setCountryName(row.getCell(1).getStringCellValue());
                info.setCountryDisplayName(row.getCell(1).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(2))) {
                row.getCell(2).setCellType(CellType.STRING);
                info.setNetworkCode(row.getCell(2).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(3))) {
                row.getCell(3).setCellType(CellType.STRING);
                info.setNetworkAliases(row.getCell(3).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(4))) {
                row.getCell(4).setCellType(CellType.STRING);
                info.setNetworkName(row.getCell(4).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(5))) {
                row.getCell(5).setCellType(CellType.STRING);
                info.setPrefix(row.getCell(5).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(6))) {
                row.getCell(6).setCellType(CellType.NUMERIC);
                final BigDecimal price = BigDecimal.valueOf(row.getCell(6).getNumericCellValue());
                info.setPrice(price);
                info.setCostPrice(price);
                info.setDefaultPrice(price);
            }
            if (Objects.nonNull(row.getCell(7))) {
                row.getCell(7).setCellType(CellType.STRING);
                info.setCurrency(row.getCell(7).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(0)) && Objects.nonNull(row.getCell(5))) {
                info.setAgentId(agentId);
                info.setType("mobile");
            }
            if (StringUtils.isEmpty(info.getCountryName()) || StringUtils.isEmpty(info.getCountryDisplayName()) || StringUtils.isEmpty(info.getCountryCode())) {
                continue;
            }
            smsPricingList.add(info);
        }
        return smsPricingList;
    }

    private List<SmsPricing> readNexmoExcel(InputStream inputStream, String agentId) throws IOException, InvalidFormatException {
        List<SmsPricing> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (Objects.isNull(row)) {
                continue;
            }
            SmsPricing info = new SmsPricing();
            if (Objects.nonNull(row.getCell(0))) {
                info.setCountryCode(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                info.setCountryName(row.getCell(1).getStringCellValue());
                info.setCountryDisplayName(row.getCell(1).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(2))) {
                info.setNetworkCode(row.getCell(2).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(3))) {
                info.setNetworkAliases(row.getCell(3).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(4))) {
                info.setNetworkName(row.getCell(4).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(5))) {
                info.setPrefix(row.getCell(5).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(6))) {
                final BigDecimal price = BigDecimal.valueOf(row.getCell(6).getNumericCellValue());
                info.setPrice(price);
                info.setCostPrice(price);
                info.setDefaultPrice(price);
            }
            if (Objects.nonNull(row.getCell(0)) && Objects.nonNull(row.getCell(5))) {
                info.setCurrency("EUR");
                info.setAgentId(agentId);
                info.setType("mobile");
            }
            if (StringUtils.isEmpty(info.getCountryName()) || StringUtils.isEmpty(info.getCountryDisplayName()) || StringUtils.isEmpty(info.getCountryCode())) {
                continue;
            }
            smsPricingList.add(info);
        }
        return smsPricingList;
    }

    private List<SmsPricing> readSinchExcel(InputStream inputStream, String agentId) throws IOException, InvalidFormatException {
        List<SmsPricing> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (Objects.isNull(row)) {
                continue;
            }
            SmsPricing info = new SmsPricing();
            if (Objects.nonNull(row.getCell(0))) {
                info.setCountryCode(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                final BigDecimal price = BigDecimal.valueOf(row.getCell(1).getNumericCellValue());
                info.setPrice(price);
                info.setCostPrice(price);
                info.setDefaultPrice(price);
            }
            if (Objects.nonNull(row.getCell(2))) {
                info.setCountryName(row.getCell(2).getStringCellValue());
                info.setCountryDisplayName(row.getCell(2).getStringCellValue());
            }

            if (Objects.nonNull(row.getCell(3))) {
                row.getCell(3).setCellType(CellType.STRING);
                info.setPrefix(row.getCell(3).getStringCellValue());
            }
            info.setCurrency("EUR");
            info.setAgentId(agentId);
            info.setType("mobile");
            if (StringUtils.isEmpty(info.getCountryName()) || StringUtils.isEmpty(info.getCountryDisplayName()) || StringUtils.isEmpty(info.getCountryCode())) {
                continue;
            }
            smsPricingList.add(info);
        }
        return smsPricingList;
    }

    private List<SmsPricing> readCmExcel(InputStream inputStream, String agentId) throws IOException, InvalidFormatException {
        List<SmsPricing> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (Objects.isNull(row)) {
                continue;
            }
            SmsPricing info = new SmsPricing();
            if (Objects.nonNull(row.getCell(0))) {
                info.setCountryName(row.getCell(0).getStringCellValue());
                info.setCountryDisplayName(row.getCell(0).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(1))) {
                info.setCountryCode(row.getCell(1).getStringCellValue());
            }

            if (Objects.nonNull(row.getCell(3))) {
                row.getCell(3).setCellType(CellType.STRING);
                String str = row.getCell(3).getStringCellValue();
                StringBuffer sb = new StringBuffer();
                String prefix = "";
                String[] strs = str.split(",");
                if (strs.length > 0) {
                    for (String sstr : strs) {
                        String ss = sstr.substring(2);
                        sb.append(ss).append(",");
                    }
                    String ss = sb.toString();
                    prefix = ss.substring(0, ss.length() - 1);
                } else {
                    prefix = str.substring(0, str.length() - 1);
                }

                info.setPrefix(prefix);
            }
            if (Objects.nonNull(row.getCell(5))) {
                row.getCell(5).setCellType(CellType.STRING);
                final BigDecimal price = new BigDecimal(row.getCell(5).getStringCellValue());
                info.setPrice(price);
                info.setCostPrice(price);
                info.setDefaultPrice(price);
            }
            info.setCurrency("EUR");
            info.setAgentId(agentId);
            info.setType("mobile");
            if (StringUtils.isEmpty(info.getCountryName()) || StringUtils.isEmpty(info.getCountryDisplayName()) || StringUtils.isEmpty(info.getCountryCode())) {
                continue;
            }
            smsPricingList.add(info);
        }
        return smsPricingList;
    }

    private List<SmsPricing> readCSVFile(InputStream inStream, String agentId) throws IOException {
        List<SmsPricing> smsPricingList = new ArrayList<>();
        BufferedReader reader = new BufferedReader(new InputStreamReader(inStream, StandardCharsets.UTF_8));
        reader.readLine();//第一行信息，为标题信息，不用,如果需要，注释掉
        String line;
        int num = 0;
        while ((line = reader.readLine()) != null) {
            num++;
            String[] item = line.replace("\"", "").split(",");//CSV格式文件为逗号分隔符文件，这里根据逗号切分
            final SmsPricing smsPricing = SmsPricing.builder()
                    .agentId(agentId)
                    .countryName(item[0].replaceAll("", ""))
                    .countryDisplayName(item[0])
                    .networkName(item[1])
                    .mcc(item[4])
                    .mnc(item[5])
                    .costPrice(BigDecimal.valueOf(Double.valueOf(item[6])))
                    .price(BigDecimal.valueOf(Double.valueOf(item[6])))
                    .currency(item[7])
                    .build();
            log.info(item[0]);
            smsPricingList.add(smsPricing);

        }
        log.info("###读取行数为:" + num);
        return smsPricingList;
    }

    private List<SmsPricing> readNexmoCSVFile(InputStream inStream, String agentId) throws IOException {
        List<SmsPricing> smsPricingList = new ArrayList<>();
        BufferedReader reader = new BufferedReader(new InputStreamReader(inStream, StandardCharsets.UTF_8));
        reader.readLine();//第一行信息，为标题信息，不用,如果需要，注释掉
        String line;
        int num = 0;
        while ((line = reader.readLine()) != null) {
            num++;
            String[] item = line.replace("\"", "").split(",");//CSV格式文件为逗号分隔符文件，这里根据逗号切分
            final SmsPricing smsPricing = SmsPricing.builder()
                    .agentId(agentId)
                    .countryName(item[0].replaceAll("", ""))
                    .countryDisplayName(item[0])
                    .networkName(item[1])
                    .mcc(item[4])
                    .mnc(item[5])
                    .costPrice(BigDecimal.valueOf(Double.valueOf(item[6])))
                    .price(BigDecimal.valueOf(Double.valueOf(item[6])))
                    .currency(item[7])
                    .build();
            log.info(item[0]);
            smsPricingList.add(smsPricing);

        }
        log.info("###读取行数为:" + num);
        return smsPricingList;
    }

    private List<SmsPricing> readSinchCSVFile(InputStream inStream, String agentId) throws IOException {
        List<SmsPricing> smsPricingList = new ArrayList<>();
        BufferedReader reader = new BufferedReader(new InputStreamReader(inStream, StandardCharsets.UTF_8));
        reader.readLine();//第一行信息，为标题信息，不用,如果需要，注释掉
        String line;
        int num = 0;
        while ((line = reader.readLine()) != null) {
            num++;
            String[] item = line.replace("\"", "").split(",");//CSV格式文件为逗号分隔符文件，这里根据逗号切分
            final SmsPricing smsPricing = SmsPricing.builder()
                    .agentId(agentId)
                    .countryName(item[0].replaceAll("", ""))
                    .countryDisplayName(item[0])
                    .networkName(item[1])
                    .mcc(item[4])
                    .mnc(item[5])
                    .costPrice(BigDecimal.valueOf(Double.valueOf(item[6])))
                    .price(BigDecimal.valueOf(Double.valueOf(item[6])))
                    .currency(item[7])
                    .build();
            log.info(item[0]);
            smsPricingList.add(smsPricing);

        }
        log.info("###读取行数为:" + num);
        return smsPricingList;
    }

    private List<SmsPricing> readCmCSVFile(InputStream inStream, String agentId) throws IOException {
        List<SmsPricing> smsPricingList = new ArrayList<>();
        BufferedReader reader = new BufferedReader(new InputStreamReader(inStream, StandardCharsets.UTF_8));
        reader.readLine();//第一行信息，为标题信息，不用,如果需要，注释掉
        String line;
        int num = 0;
        while ((line = reader.readLine()) != null) {
            num++;
            String[] item = line.replace("\"", "").split(",");//CSV格式文件为逗号分隔符文件，这里根据逗号切分
            final SmsPricing smsPricing = SmsPricing.builder()
                    .agentId(agentId)
                    .countryName(item[0].replaceAll("", ""))
                    .countryDisplayName(item[0])
                    .networkName(item[1])
                    .mcc(item[4])
                    .mnc(item[5])
                    .costPrice(BigDecimal.valueOf(Double.valueOf(item[6])))
                    .price(BigDecimal.valueOf(Double.valueOf(item[6])))
                    .currency(item[7])
                    .build();
            log.info(item[0]);
            smsPricingList.add(smsPricing);

        }
        log.info("###读取行数为:" + num);
        return smsPricingList;
    }

    @Override
    public IPage<SmsPricing> queryPricingPageByAgentId(Integer pageNum, Integer pageSize, String agentId, String countryName, String countryCode) {
        Page<SmsPricing> pricingPage = new Page<>(pageNum, pageSize);
        return lambdaQuery()
                .eq(SmsPricing::getAgentId, agentId)
                .eq(StringUtils.isNotEmpty(countryCode), SmsPricing::getCountryCode, countryCode)
                .eq(StringUtils.isNotEmpty(countryName), SmsPricing::getCountryName, countryName)
                .page(pricingPage);
    }

    @Override
    public Boolean updatePricingByCountry(UpdatePriceByCountryDTO priceByCountryDTO) {
        return lambdaUpdate()
                .set(Objects.nonNull(priceByCountryDTO.getCostPrice()), SmsPricing::getCostPrice, priceByCountryDTO.getCostPrice())
                //.set(Objects.nonNull(priceByCountryDTO.getPrice()), SmsPricing::getPrice, priceByCountryDTO.getPrice())
                .set(Objects.nonNull(priceByCountryDTO.getDefaultPrice()), SmsPricing::getDefaultPrice, priceByCountryDTO.getDefaultPrice())
                .eq(StringUtils.isNotEmpty(priceByCountryDTO.getCountryCode()),
                        SmsPricing::getCountryCode, priceByCountryDTO.getCountryCode())
                .eq(StringUtils.isNotEmpty(priceByCountryDTO.getCountryName()),
                        SmsPricing::getCountryName, priceByCountryDTO.getCountryName())
                .eq(SmsPricing::getAgentId, priceByCountryDTO.getAgentId())
                .update();
    }

    @Override
    public void downloadAllPrice(HttpServletRequest request, HttpServletResponse response) {
        try {
            List<SmsPricing> bgmExcelDownloads = this.list();
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("获取excel测试表格");

            HSSFRow row = null;

			/*row = sheet.createRow(0);//创建第一个单元格
			row.setHeight((short) (26.25 * 20));
			row.createCell(0).setCellValue("用户信息列表");//为第一行单元格设值
*/
            /*为标题设计空间
             * firstRow从第1行开始
             * lastRow从第0行结束
             *
             *从第1个单元格开始
             * 从第3个单元格结束
             */
            //CellRangeAddress rowRegion = new CellRangeAddress(0, 0, 0, 1);
            //sheet.addMergedRegion(rowRegion);
            row = sheet.createRow(0);
            row.setHeight((short) (22.50 * 20));//设置行高
            row.createCell(0).setCellValue("Id(请勿修改)");//为第一个单元格设值
            row.createCell(1).setCellValue("代理商");//为第二个单元格设值
            row.createCell(2).setCellValue("国家简码");//为第三个单元格设值
            row.createCell(3).setCellValue("国家名称");
            row.createCell(4).setCellValue("国家显示名称");
            row.createCell(5).setCellValue("网络");
            row.createCell(6).setCellValue("网络区域码");
            row.createCell(7).setCellValue("运营商名称");
            row.createCell(8).setCellValue("前缀");
            row.createCell(9).setCellValue("成本价");
            row.createCell(10).setCellValue("售价");
            row.createCell(11).setCellValue("默认价格");
            row.createCell(12).setCellValue("货币单位");
            row.createCell(13).setCellValue("type类型");
            row.createCell(14).setCellValue("mcc");
            row.createCell(15).setCellValue("mnc");

            //遍历所获取的数据
            for (int i = 0; i < bgmExcelDownloads.size(); i++) {
                row = sheet.createRow(i + 1);
                SmsPricing sp = bgmExcelDownloads.get(i);
                row.createCell(0).setCellValue(sp.getOperatorId());
                row.createCell(1).setCellValue(sp.getAgentId());
                row.createCell(2).setCellValue(sp.getCountryCode());
                row.createCell(3).setCellValue(sp.getCountryName());
                row.createCell(4).setCellValue(sp.getCountryDisplayName());
                row.createCell(5).setCellValue(sp.getNetworkCode());
                row.createCell(6).setCellValue(sp.getNetworkAliases());
                row.createCell(7).setCellValue(sp.getNetworkName());
                row.createCell(8).setCellValue(sp.getPrefix());
                row.createCell(9).setCellValue(String.valueOf(sp.getCostPrice()));
                row.createCell(10).setCellValue(String.valueOf(sp.getPrice()));
                row.createCell(11).setCellValue(String.valueOf(sp.getDefaultPrice()));
                row.createCell(12).setCellValue(sp.getCurrency());
                row.createCell(13).setCellValue(sp.getType());
                row.createCell(14).setCellValue(sp.getMcc());
                row.createCell(15).setCellValue(sp.getMnc());
            }
            sheet.setDefaultRowHeight((short) (16.5 * 20));
            //列宽自适应
            for (int i = 0; i <= 13; i++) {
                sheet.autoSizeColumn(i);
            }


            OutputStream os = response.getOutputStream();
            String ename = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
            String fileName = "excel_" + ename + ".xls";
            response.setHeader("Access-Control-Expose-Headers", SessionKeys.SMS_TOKEN + ",Content-Disposition");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("Content-Type", "application/vnd.ms-excel");

            wb.write(os);
            response.flushBuffer();
            os.flush();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    @Override
    public Result uploadAllPrice(MultipartFile file) {
        List<SmsPricing> infoList = null;
        try {
            infoList = readSmsPriceExcel(file.getInputStream());

        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        if (this.saveOrUpdateBatch(infoList)) {
            return ResultGenerator.genSuccessResult("导入Excel成功!");
        } else {
            return ResultGenerator.genClientFailResult("导入Excel失败!");
        }
    }

    private List<SmsPricing> readSmsPriceExcel(InputStream inputStream) throws IOException, InvalidFormatException {
        List<SmsPricing> smsPricingList = new ArrayList<>();
        Workbook wb = WorkbookFactory.create(inputStream); //new HSSFWorkbook(inputStream);
        Sheet sheet = wb.getSheetAt(0);
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            SmsPricing info = new SmsPricing();
            info.setOperatorId(row.getCell(0).getStringCellValue());
            info.setAgentId(row.getCell(1).getStringCellValue());
            info.setCountryCode(row.getCell(2).getStringCellValue());
            info.setCountryName(row.getCell(3).getStringCellValue());
            info.setCountryDisplayName(row.getCell(4).getStringCellValue());
            if (Objects.nonNull(row.getCell(5))) {
                row.getCell(5).setCellType(CellType.STRING);
                info.setNetworkCode(row.getCell(5).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(6))) {
                row.getCell(6).setCellType(CellType.STRING);
                info.setNetworkAliases(row.getCell(6).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(7))) {
                row.getCell(7).setCellType(CellType.STRING);
                info.setNetworkName(row.getCell(7).getStringCellValue());
            }
            info.setPrefix(row.getCell(8).getStringCellValue());
            if (Objects.nonNull(row.getCell(9))) {
                row.getCell(9).setCellType(CellType.STRING);
                info.setCostPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(9).getStringCellValue())));
            }
            if (Objects.nonNull(row.getCell(10))) {
                row.getCell(10).setCellType(CellType.STRING);
                info.setPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(10).getStringCellValue())));
            }
            if (Objects.nonNull(row.getCell(11))) {
                row.getCell(11).setCellType(CellType.STRING);
                info.setDefaultPrice(BigDecimal.valueOf(Double.valueOf(row.getCell(11).getStringCellValue())));
            }

            info.setCurrency(row.getCell(12).getStringCellValue());
            info.setType(row.getCell(13).getStringCellValue());

            if (Objects.nonNull(row.getCell(14))) {
                row.getCell(14).setCellType(CellType.STRING);
                info.setMcc(row.getCell(14).getStringCellValue());
            }
            if (Objects.nonNull(row.getCell(15))) {
                row.getCell(15).setCellType(CellType.STRING);
                info.setMnc(row.getCell(15).getStringCellValue());
            }

            smsPricingList.add(info);
        }
        return smsPricingList;
    }

    @Override
    public Result updateBatchPrice(SmsUpdatePricingDTO smsUpdatePricingDTO) {
        List<SmsPricing> infoList = new ArrayList<>();
        List<String> ids = smsUpdatePricingDTO.getOperatorIds();
        if (smsUpdatePricingDTO.getSymbol().equals("+")) {
            for (String operatorId : ids) {
                SmsPricing info = this.getById(operatorId);
                info.setPrice(info.getCostPrice().add(smsUpdatePricingDTO.getCountValue()));
                infoList.add(info);
            }
        } else {
            for (String operatorId : ids) {
                SmsPricing info = this.getById(operatorId);
                info.setPrice(info.getCostPrice().multiply(smsUpdatePricingDTO.getCountValue()));
                infoList.add(info);
            }
        }
        if (this.updateBatchById(infoList)) {
            return ResultGenerator.genSuccessResult("批量修改价格成功!");
        } else {
            return ResultGenerator.genClientFailResult("批量修改价格失败!");
        }
    }


    @Override
    public List<SmsPricing> queryPricingList() {
        return lambdaQuery()
                .list();
    }

    @Override
    public Boolean updateStatusByAgentIds(List<String> agentIds, Boolean status) {
        return lambdaUpdate()
                .set(SmsPricing::getStatus, status)
                .in(SmsPricing::getAgentId, agentIds)
                .update();
    }

    @Override
    public List<SmsPricing> querySmsPricing(List<String> agentIds) {
        return lambdaQuery()
                .in(SmsPricing::getAgentId, agentIds)
                .list();

    }

    @Override
    public List<SmsPricing> queryUserAllowOperatorByConditions(QueryPricingByConditionsDTO conditionsDTO) {
        return lambdaQuery()
                .eq(StringUtils.isNotEmpty(conditionsDTO.getCountryCode()), SmsPricing::getCountryCode, conditionsDTO.getCountryCode())
                .eq(StringUtils.isNotEmpty(conditionsDTO.getCountryName()), SmsPricing::getCountryName, conditionsDTO.getCountryName())
                .eq(StringUtils.isNotEmpty(conditionsDTO.getPrefix()), SmsPricing::getPrefix, conditionsDTO.getPrefix())
                .eq(StringUtils.isNotEmpty(conditionsDTO.getNetworkCode()), SmsPricing::getNetworkCode, conditionsDTO.getNetworkCode())
                .eq(StringUtils.isNotEmpty(conditionsDTO.getNetworkName()), SmsPricing::getNetworkName, conditionsDTO.getNetworkName())
                .in(CollectionUtils.isNotEmpty(conditionsDTO.getOperatorIds()), SmsPricing::getOperatorId, conditionsDTO.getOperatorIds())
                .orderByAsc(SmsPricing::getCountryCode)
                .list();
    }

    @Override
    public void downloadAgentAllPrice(HttpServletRequest request, HttpServletResponse response,
                                      SmsPricingDTO smsPricingDTO) {
        try {
            //查询用户角色

            List<SmsPricing> bgmExcelDownloads = null;
            QueryWrapper<SmsPricing> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("agent_id", smsPricingDTO.getAgentId());
            bgmExcelDownloads = this.list(queryWrapper);
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("获取excel表格数据");

            HSSFRow row = null;

            row = sheet.createRow(0);
            row.setHeight((short) (22.50 * 20));//设置行高
            row.createCell(0).setCellValue("operatorId(请勿修改)");//为第一个单元格设值
            row.createCell(1).setCellValue("代理商ID(请勿修改)");//为第二个单元格设值
            row.createCell(2).setCellValue("国家简码");//为第三个单元格设值
            row.createCell(3).setCellValue("国家名称");
            row.createCell(4).setCellValue("国家显示名称");
            row.createCell(5).setCellValue("网络");
            row.createCell(6).setCellValue("网络区域码");
            row.createCell(7).setCellValue("运营商名称");
            row.createCell(8).setCellValue("前缀");
            row.createCell(9).setCellValue("成本价");
            row.createCell(10).setCellValue("售价");
            row.createCell(11).setCellValue("默认价格");
            row.createCell(12).setCellValue("货币单位");
            row.createCell(13).setCellValue("type类型");
            row.createCell(14).setCellValue("mcc");
            row.createCell(15).setCellValue("mnc");
            row.createCell(16).setCellValue("userId(请勿修改)");
            row.createCell(17).setCellValue("allowOperatorId(请勿修改)");
            //遍历所获取的数据
            for (int i = 0; i < bgmExcelDownloads.size(); i++) {
                row = sheet.createRow(i + 1);
                SmsPricing sp = bgmExcelDownloads.get(i);
                row.createCell(0).setCellValue(sp.getOperatorId());
                row.createCell(1).setCellValue(sp.getAgentId());
                row.createCell(2).setCellValue(sp.getCountryCode());
                row.createCell(3).setCellValue(sp.getCountryName());
                row.createCell(4).setCellValue(sp.getCountryDisplayName());
                row.createCell(5).setCellValue(sp.getNetworkCode());
                row.createCell(6).setCellValue(sp.getNetworkAliases());
                row.createCell(7).setCellValue(sp.getNetworkName());
                row.createCell(8).setCellValue(sp.getPrefix());
                row.createCell(9).setCellValue(String.valueOf(sp.getCostPrice()));
                row.createCell(10).setCellValue(String.valueOf(sp.getPrice()));
                row.createCell(11).setCellValue(String.valueOf(sp.getDefaultPrice()));
                row.createCell(12).setCellValue(sp.getCurrency());
                row.createCell(13).setCellValue(sp.getType());
                row.createCell(14).setCellValue(sp.getMcc());
                row.createCell(15).setCellValue(sp.getMnc());
            }
            sheet.setDefaultRowHeight((short) (16.5 * 20));
            //列宽自适应
            for (int i = 0; i <= 13; i++) {
                sheet.autoSizeColumn(i);
            }


            OutputStream os = response.getOutputStream();
            String ename = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
            String fileName = "agentExcel_" + ename + ".xls";
            response.setHeader("Access-Control-Expose-Headers", SessionKeys.SMS_TOKEN + ",Content-Disposition");
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setHeader("Content-Type", "application/vnd.ms-excel");

            wb.write(os);
            response.flushBuffer();
            os.flush();
            os.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    @Override
    public Result uploadAgentAllPrice(MultipartFile file) {
        List<SmsPricing> infoList = null;
        try {
            infoList = readSmsPriceExcel(file.getInputStream());

        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        if (this.updateBatchById(infoList)) {
            return ResultGenerator.genSuccessResult("导入Excel成功!");
        } else {
            return ResultGenerator.genClientFailResult("导入Excel失败!");
        }
    }

    @Override
    public Result deleteAgentPrice(SmsUpdatePricingDTO smsUpdatePricingDTO) {
        if (smsUpdatePricingDTO.getOperatorIds() != null) {
            if (this.removeByIds(smsUpdatePricingDTO.getOperatorIds())) {
                return ResultGenerator.genSuccessResult("删除代理商价格成功!");
            } else {
                return ResultGenerator.genClientFailResult("删除代理商价格失败!");
            }
        }
        return ResultGenerator.genSuccessResult("删除代理商价格成功!");
    }

    @Override
    public Result deleteAgentAllPrice(SmsUpdatePricingDTO smsUpdatePricingDTO) {
        if (smsUpdatePricingDTO.getAgentId() != null) {
            QueryWrapper<SmsPricing> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("agent_id", smsUpdatePricingDTO.getAgentId());

            if (this.remove(queryWrapper)) {
                return ResultGenerator.genSuccessResult("删除代理商所有价格成功!");
            } else {
                return ResultGenerator.genClientFailResult("删除代理商所有价格失败!");
            }
        }
        return ResultGenerator.genSuccessResult("删除代理商所有价格成功!");
    }
}
